package com.huaxia.dao.deptLevels;

import com.huaxia.pojo.deptLevels.BranchStandard;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/08/05 16:03
 */

public interface BranchStandardMapper {

    @Select("select sb.branchsname,NVL(sa.standardNumAS,0)standardNumAS,NVL(bm.standardNumBM,0)standardNumBM,NVL(bc.standardNumBC,0)standardNumBC from SD_BRANCH sb left join\n" +
            " (select SUBSTR(a.brancharea,1,6)branchcode,count(*)standardNumBC from(\n" +
            " select * from FACT_GX_PROJECT_TRACE t where SUBSTR(t.quarter,1,4)=to_char(sysdate,'yyyy') and SUBSTR(t.quarter,5,5)=(select \n" +
            "      CASE\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '01' AND '03' THEN 1\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '04' AND '06' THEN 2\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '07' AND '09' THEN 3\n" +
            "      ELSE 4 END from dual)\n" +
            ")a where a.fir_finalist='是' and flag='03' group by SUBSTR(a.brancharea,1,6)\n" +
            ") bc on sb.branchcode=bc.branchcode left join\n" +
            " (select SUBSTR(a.brancharea,1,6)branchcode,count(*)standardNumBM from(\n" +
            " select * from FACT_GX_PROJECT_TRACE t where SUBSTR(t.quarter,1,4)=to_char(sysdate,'yyyy') and SUBSTR(t.quarter,5,5)=(select \n" +
            "      CASE\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '01' AND '03' THEN 1\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '04' AND '06' THEN 2\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '07' AND '09' THEN 3\n" +
            "      ELSE 4 END from dual)\n" +
            ")a where a.fir_finalist='是' and flag='02' group by SUBSTR(a.brancharea,1,6)\n" +
            ")bm on sb.branchcode=bm.branchcode left join\n" +
            " (select SUBSTR(a.brancharea,1,6)branchcode,count(*)standardNumAS from(\n" +
            " select * from FACT_GX_PROJECT_TRACE t where SUBSTR(t.quarter,1,4)=to_char(sysdate,'yyyy') and SUBSTR(t.quarter,5,5)=(select \n" +
            "      CASE\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '01' AND '03' THEN 1\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '04' AND '06' THEN 2\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '07' AND '09' THEN 3\n" +
            "      ELSE 4 END from dual)\n" +
            ")a where a.fir_finalist='是' and flag='01' group by SUBSTR(a.brancharea,1,6)\n" +
            ")sa on sb.branchcode=sa.branchcode order by standardNumBC DESC")
    List<BranchStandard> getBranchFir();


    @Select("select sb.branchsname,NVL(sa.standardNumAS,0)standardNumAS,NVL(bm.standardNumBM,0)standardNumBM,NVL(bc.standardNumBC,0)standardNumBC from SD_BRANCH sb left join\n" +
            " (select SUBSTR(a.brancharea,1,6)branchcode,count(*)standardNumBC from(\n" +
            " select * from FACT_GX_PROJECT_TRACE t where SUBSTR(t.quarter,1,4)=to_char(sysdate,'yyyy') and SUBSTR(t.quarter,5,5)=(select \n" +
            "      CASE\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '01' AND '03' THEN 1\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '04' AND '06' THEN 2\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '07' AND '09' THEN 3\n" +
            "      ELSE 4 END from dual)\n" +
            ")a where a.sec_finalist='是' and flag='03' group by SUBSTR(a.brancharea,1,6)\n" +
            ") bc on sb.branchcode=bc.branchcode left join\n" +
            " (select SUBSTR(a.brancharea,1,6)branchcode,count(*)standardNumBM from(\n" +
            " select * from FACT_GX_PROJECT_TRACE t where SUBSTR(t.quarter,1,4)=to_char(sysdate,'yyyy') and SUBSTR(t.quarter,5,5)=(select \n" +
            "      CASE\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '01' AND '03' THEN 1\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '04' AND '06' THEN 2\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '07' AND '09' THEN 3\n" +
            "      ELSE 4 END from dual)\n" +
            ")a where a.sec_finalist='是' and flag='02' group by SUBSTR(a.brancharea,1,6)\n" +
            ")bm on sb.branchcode=bm.branchcode left join\n" +
            " (select SUBSTR(a.brancharea,1,6)branchcode,count(*)standardNumAS from(\n" +
            " select * from FACT_GX_PROJECT_TRACE t where SUBSTR(t.quarter,1,4)=to_char(sysdate,'yyyy') and SUBSTR(t.quarter,5,5)=(select \n" +
            "      CASE\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '01' AND '03' THEN 1\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '04' AND '06' THEN 2\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '07' AND '09' THEN 3\n" +
            "      ELSE 4 END from dual)\n" +
            ")a where a.sec_finalist='是' and flag='01' group by SUBSTR(a.brancharea,1,6)\n" +
            ")sa on sb.branchcode=sa.branchcode order by standardNumBC DESC")
    List<BranchStandard> getBranchSec();


    @Select("select sb.branchsname,NVL(sa.standardNumAS,0)standardNumAS,NVL(bm.standardNumBM,0)standardNumBM,NVL(bc.standardNumBC,0)standardNumBC from SD_BRANCH sb left join\n" +
            " (select SUBSTR(a.brancharea,1,6)branchcode,count(*)standardNumBC from(\n" +
            " select * from FACT_GX_PROJECT_TRACE t where SUBSTR(t.quarter,1,4)=to_char(sysdate,'yyyy') and SUBSTR(t.quarter,5,5)=(select \n" +
            "      CASE\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '01' AND '03' THEN 1\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '04' AND '06' THEN 2\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '07' AND '09' THEN 3\n" +
            "      ELSE 4 END from dual)\n" +
            ")a where a.thi_finalist='是' and flag='03' group by SUBSTR(a.brancharea,1,6)\n" +
            ") bc on sb.branchcode=bc.branchcode left join\n" +
            " (select SUBSTR(a.brancharea,1,6)branchcode,count(*)standardNumBM from(\n" +
            " select * from FACT_GX_PROJECT_TRACE t where SUBSTR(t.quarter,1,4)=to_char(sysdate,'yyyy') and SUBSTR(t.quarter,5,5)=(select \n" +
            "      CASE\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '01' AND '03' THEN 1\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '04' AND '06' THEN 2\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '07' AND '09' THEN 3\n" +
            "      ELSE 4 END from dual)\n" +
            ")a where a.thi_finalist='是' and flag='02' group by SUBSTR(a.brancharea,1,6)\n" +
            ")bm on sb.branchcode=bm.branchcode left join\n" +
            " (select SUBSTR(a.brancharea,1,6)branchcode,count(*)standardNumAS from(\n" +
            " select * from FACT_GX_PROJECT_TRACE t where SUBSTR(t.quarter,1,4)=to_char(sysdate,'yyyy') and SUBSTR(t.quarter,5,5)=(select \n" +
            "      CASE\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '01' AND '03' THEN 1\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '04' AND '06' THEN 2\n" +
            "      WHEN to_char(sysdate , 'mm') BETWEEN '07' AND '09' THEN 3\n" +
            "      ELSE 4 END from dual)\n" +
            ")a where a.thi_finalist='是' and flag='01' group by SUBSTR(a.brancharea,1,6)\n" +
            ")sa on sb.branchcode=sa.branchcode order by standardNumBC DESC")
    List<BranchStandard> getBranchThi();
}
